package main

import (
	mysql "database/sql"
	"strconv"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

func TestDbConn(fields DbConnFields) error {
	dao, err := mysql.Open("mysql", fields.DbUser+":"+fields.DbPass+"@tcp("+fields.DbHost+":"+
		strconv.Itoa(fields.DbPort)+")/"+fields.DbName+"?charset="+fields.DbCharset)
	defer dao.Close()
	_, err = dao.Prepare("select * from mysql.db ")
	return err
}

func ExecuteWithDbConn(sql string, values []interface{}, fields DbConnFields) (map[string]interface{}, error) {
	rs := make(map[string]interface{})
	dao, err := mysql.Open("mysql", fields.DbUser+":"+fields.DbPass+"@tcp("+fields.DbHost+":"+
		strconv.Itoa(fields.DbPort)+")/"+fields.DbName+"?charset="+fields.DbCharset)
	defer dao.Close()
	if err != nil {
		rs["code"] = 204
		return rs, err
	}
	stmt, err := dao.Prepare(sql)
	if err != nil {
		rs["code"] = 204
		return rs, err
	}
	rows, err := stmt.Query(values...)
	if err != nil {
		rs["code"] = 204
		return rs, err
	}

	columns, err := rows.Columns()
	vs := make([]mysql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))

	for i := range vs {
		scans[i] = &vs[i]
	}

	var result []map[string]interface{}
	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(map[string]interface{})

		for i, col := range vs {
			if col != nil {
				each[columns[i]] = FilterHolder(string(col))
			} else {
				each[columns[i]] = nil
			}
		}

		result = append(result, each)
	}
	rs["code"] = 200
	//data, _ := json.Marshal(result)
	rs["rows"] = result
	return rs, err
}
func ExecuteWithDbConnWithWrite(sql string, values []interface{}, fields DbConnFields, tablename string, fileName string) error {
	dao, err := mysql.Open("mysql", fields.DbUser+":"+fields.DbPass+"@tcp("+fields.DbHost+":"+
		strconv.Itoa(fields.DbPort)+")/"+fields.DbName+"?charset="+fields.DbCharset)
	defer dao.Close()
	if err != nil {
		return err
	}
	stmt, err := dao.Prepare(sql)
	if err != nil {
		return err
	}
	rows, err := stmt.Query(values...)
	if err != nil {
		return err
	}

	columns, err := rows.Columns()
	vs := make([]mysql.RawBytes, len(columns))
	scans := make([]interface{}, len(columns))

	for i := range vs {
		scans[i] = &vs[i]
	}

	for rows.Next() {
		_ = rows.Scan(scans...)
		each := make(map[string]interface{})

		for i, col := range vs {
			if col != nil {
				each[columns[i]] = FilterHolder(string(col))
			} else {
				each[columns[i]] = nil
			}
		}
		strExport := "INSERT INTO `" + tablename + "` (" //+strings.Join(allFields, ",")+") VALUES ("
		var ks []string
		var vs []string
		for k, v := range each {
			ks = append(ks, "`"+k+"`")
			elStr := "''"
			if v == nil {
				elStr = "null"
			} else if len(v.(string)) > 0 {
				elStr = "'" + escape(v.(string)) + "'"
			}
			vs = append(vs, elStr)
		}
		strExport += strings.Join(ks, ",") + ") VALUES (" + strings.Join(vs, ",") + ");\n"
		writeToFile(fileName, strExport, true)
	}
	return err
}
func FilterHolder(content string) string {
	newContent := ""
	for _, value := range content {
		if value != 65533 {
			newContent += string(value)
		}
	}
	return newContent
}
